/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package cs425proj.servlets;

import cs425proj.model.*;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.*;
import javax.servlet.ServletException;
import javax.servlet.http.*;

/**
 *
 * @author prateekpatil
 */
public class PurchaseTicket extends HttpServlet {

    /**
     * Processes requests for both HTTP
     * <code>GET</code> and
     * <code>POST</code> methods.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        response.setContentType("text/html;charset=UTF-8");
        PrintWriter out = response.getWriter();
        try {
            /*
             * TODO output your page here. You may use following sample code.
             */
         ticket tobj = new ticket();   
         ResultSet rs;
        try{
        connectDB obj = new connectDB();
        Connection con =  obj.startConnection();
        HttpSession session= request.getSession();
        String name = (String) session.getAttribute("name");
        Statement stm;
        stm =con.createStatement();
        MemberDetail mdobj = new MemberDetail();
        mdobj.selectMemberData(name);
        String showtime=request.getParameter("showtime");
        String movie = request.getParameter("movie");
        String date = request.getParameter("date");
        String location = request.getParameter("location");
        String theater = request.getParameter("theater");
        long creditcard_number =Long.parseLong(request.getParameter("creditcard_number"));
         String cc_type=request.getParameter("cc_type");
        String expire_date=request.getParameter("expire_date");
        int cvv_number=Integer.parseInt(request.getParameter("cvv_number"));
        int amount=Integer.parseInt(request.getParameter("seat"))*12;
       
        String qry_screen_id ="select screen_id from screen where screen_id in (select screen_id from schedule where timing in '"+showtime+"'and movie_id in (select movie_id from movie where name='"+movie+"')and TO_DATE('"+date+"','dd-mm-yyyy') between startdate and enddate)and theater_id in (select theater_id from theater where city='"+location+"'and theater_name='"+theater+"')";
        int screen_id=0; 
        rs = stm.executeQuery(qry_screen_id );
        while(rs.next()){
        screen_id=rs.getInt(1);
        }
        
        String qry_sys_date="select sysdate from dual";
        String sys_date="" ;
        rs = stm.executeQuery(qry_sys_date );
        while(rs.next()){
        sys_date=rs.getString(1);
        }
         
             PreparedStatement pstmt;
             PreparedStatement pstmt2;
             PreparedStatement pstmt3;
            if(name==null){
        //            connectDB objcbd = new connectDB();
        //            Connection con1 =  objcbd.startConnection();
                    String qry1 = "insert into guest values(seq_guest_id.nextval,'"+request.getParameter("f_name")+"','"+request.getParameter("l_name")+"','"+Integer.parseInt(request.getParameter("phone"))+"','"+request.getParameter("email")+"' )";
                    PreparedStatement pstmt1 = con.prepareStatement(qry1);
                    pstmt1.execute();
                    String qry_guest_id="select guest_id from guest where phone='"+request.getParameter("phone")+"'";
                    int guest_id=0;
                            rs = stm.executeQuery(qry_guest_id );
                                while(rs.next()){
                                    guest_id=rs.getInt(1);
                                    }

                               
                    String qry = "insert into ticket values(seq_ticket_id.nextval ,?,?,?,?,?,?)";
                    pstmt = con.prepareStatement(qry);
                    pstmt.setString(1,showtime);
                    pstmt.setInt(2, 0);
                    pstmt.setInt(3,screen_id);
                    pstmt.setInt(4,guest_id);
                    pstmt.setString(5, request.getParameter("date"));
                    pstmt.setInt(6, 0);
                    String qrytran="insert into transaction values(seq_transaction_id.nextval,'"+creditcard_number+"','"+cc_type+"','"+expire_date+"','"+cvv_number+"','0','"+sys_date+"','"+amount+"','"+guest_id+"')";
                    pstmt2 =con.prepareStatement(qrytran);
            }
            else{
                        
                        String qry = "insert into ticket values(seq_ticket_id.nextval ,?,?,?,?,?,?)";

                    pstmt = con.prepareStatement(qry);
                    pstmt.setString(1,showtime);
                    pstmt.setInt(2,mdobj.getMember_id());
                    pstmt.setInt(3,screen_id);
                    pstmt.setInt(4,0);
                    pstmt.setString(5, request.getParameter("date"));
                    pstmt.setInt(6, 0);
                    String qrytran="insert into transaction values(seq_transaction_id.nextval,'"+creditcard_number+"','"+cc_type+"','"+expire_date+"','"+cvv_number+"','"+mdobj.getMember_id()+"','"+sys_date+"','"+amount+"','0')";
                    pstmt2 =con.prepareStatement(qrytran);
                    String qryupdate = "update member set total_points=total_points+2 where member_id = ";

                   pstmt3 = con.prepareStatement(qryupdate);
                    
            }
            
                      
           pstmt.execute();
           pstmt2.execute();
           pstmt3.execute();
           
            
            
        
        obj.closeConnection();
        }catch(SQLException e){e.printStackTrace();}
            
            
            out.println("<html>");
            out.println("<head>");
            out.println("<title>Servlet PurchaseTicket</title>");            
            out.println("</head>");
            out.println("<body align=\"center\">");
            out.println("<h1>Your transaction is succefull</h1>");
            out.println("<h1>Please click <a href=\"homepage.jsp\">Here</a> to go back</h1>");
            out.println("</body>");
            out.println("</html>");
        } finally {            
            out.close();
        }
    }

    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /**
     * Handles the HTTP
     * <code>GET</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Handles the HTTP
     * <code>POST</code> method.
     *
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        processRequest(request, response);
    }

    /**
     * Returns a short description of the servlet.
     *
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>
}
